package com.lu.dao.impl;

import com.lu.dao.BaseDao;
import com.lu.dao.NewsDao;
import com.lu.entity.News;
import com.lu.entity.Page;
import org.junit.jupiter.api.Assertions;

import java.sql.Connection;
import java.sql.PreparedStatement;
import java.sql.ResultSet;
import java.sql.SQLException;
import java.util.ArrayList;
import java.util.Date;
import java.util.List;

public class NewsDaoImpl extends BaseDao implements NewsDao {
    @Override
    public List<News> getAllNews() {
        List<News> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select * from NEWS order by ncreateDate desc";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int nid = rs.getInt("nid");
                int ntid = rs.getInt("ntid");
                String ntitle = rs.getString("ntitle");
                String nauthor = rs.getString("nauthor");
                Date ncreateDate = rs.getDate("ncreateDate");
                String npicPath = rs.getString("npicPath");
                String ncontent = rs.getString("ncontent");
                Date nmodifyDate = rs.getDate("nmodifyDate");
                String nsummary = rs.getString("nsummary");
                News news = new News(nid, ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent, nmodifyDate, nsummary);
                list.add(news);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return list;
    }

    @Override
    public List<News> getSomeNews(String keyWord) {
        List<News> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select * from NEWS where ntitle like concat('%',?,'%') order by ncreateDate desc";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, keyWord);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int nid = rs.getInt("nid");
                int ntid = rs.getInt("ntid");
                String ntitle = rs.getString("ntitle");
                String nauthor = rs.getString("nauthor");
                Date ncreateDate = rs.getDate("ncreateDate");
                String npicPath = rs.getString("npicPath");
                String ncontent = rs.getString("ncontent");
                Date nmodifyDate = rs.getDate("nmodifyDate");
                String nsummary = rs.getString("nsummary");
                News news = new News(nid, ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent, nmodifyDate, nsummary);
                list.add(news);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return list;
    }

    //@Test
    public void testGetSomeNews() {
        Assertions.assertTrue(getSomeNews("总")!=null);
    }


    @Override
    public List<News> getSomeNews(Page page) {
        List<News> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select * from NEWS order by ncreateDate desc limit ?,?";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,(page.getCurrentPage()-1)*page.getCount());
            pstmt.setInt(2,page.getCount());
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int nid = rs.getInt("nid");
                int ntid = rs.getInt("ntid");
                String ntitle = rs.getString("ntitle");
                String nauthor = rs.getString("nauthor");
                Date ncreateDate = rs.getDate("ncreateDate");
                String npicPath = rs.getString("npicPath");
                String ncontent = rs.getString("ncontent");
                Date nmodifyDate = rs.getDate("nmodifyDate");
                String nsummary = rs.getString("nsummary");
                News news = new News(nid, ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent, nmodifyDate, nsummary);
                list.add(news);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return list;
    }

    @Override
    public List<News> get20News() {
        List<News> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select * from NEWS order by ncreateDate desc LIMIT 0,20";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int nid = rs.getInt("nid");
                int ntid = rs.getInt("ntid");
                String ntitle = rs.getString("ntitle");
                String nauthor = rs.getString("nauthor");
                Date ncreateDate = rs.getDate("ncreateDate");
                String npicPath = rs.getString("npicPath");
                String ncontent = rs.getString("ncontent");
                Date nmodifyDate = rs.getDate("nmodifyDate");
                String nsummary = rs.getString("nsummary");
                News news = new News(nid, ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent, nmodifyDate, nsummary);
                list.add(news);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return list;
    }

    @Override
    public List<News> get20News(int ntid) {
        List<News> list = new ArrayList<>();
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select * from NEWS where ntid = ? order by ncreateDate desc LIMIT 0,20";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1,ntid);
            rs = pstmt.executeQuery();
            while (rs.next()) {
                int nid = rs.getInt("nid");
                String ntitle = rs.getString("ntitle");
                String nauthor = rs.getString("nauthor");
                Date ncreateDate = rs.getDate("ncreateDate");
                String npicPath = rs.getString("npicPath");
                String ncontent = rs.getString("ncontent");
                Date nmodifyDate = rs.getDate("nmodifyDate");
                String nsummary = rs.getString("nsummary");
                News news = new News(nid, ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent, nmodifyDate, nsummary);
                list.add(news);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return list;
    }

    @Override
    public int getCountNews() {
        int result = 0;
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select count(1) from NEWS";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                result = rs.getInt(1);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return result;
    }

    @Override
    public News getOneNews(int nid) {
        News news = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select * from NEWS where nid=?";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setInt(1, nid);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int ntid = rs.getInt("ntid");
                String ntitle = rs.getString("ntitle");
                String nauthor = rs.getString("nauthor");
                Date ncreateDate = rs.getDate("ncreateDate");
                String npicPath = rs.getString("npicPath");
                String ncontent = rs.getString("ncontent");
                Date nmodifyDate = rs.getDate("nmodifyDate");
                String nsummary = rs.getString("nsummary");
                news = new News(nid, ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent, nmodifyDate, nsummary);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return news;
    }

    @Override
    public News getOneNews(String ntitle) {
        News news = null;
        Connection con = null;
        PreparedStatement pstmt = null;
        String sql = "select * from NEWS where ntitle=?";
        ResultSet rs = null;
        try {
            con = getCon();
            pstmt = con.prepareStatement(sql);
            pstmt.setString(1, ntitle);
            rs = pstmt.executeQuery();
            if (rs.next()) {
                int nid = rs.getInt("nid");
                int ntid = rs.getInt("ntid");
                String nauthor = rs.getString("nauthor");
                Date ncreateDate = rs.getDate("ncreateDate");
                String npicPath = rs.getString("npicPath");
                String ncontent = rs.getString("ncontent");
                Date nmodifyDate = rs.getDate("nmodifyDate");
                String nsummary = rs.getString("nsummary");
                news = new News(nid, ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent, nmodifyDate, nsummary);
            }
        } catch (SQLException e) {
            throw new RuntimeException(e);
        } finally {
            closeAll(con, pstmt, rs);
        }
        return news;
    }

    @Override
    public int deleteOneNews(int nid) {
        String sql = "DELETE FROM NEWS WHERE nid = ?";
        Object[] params = {nid};
        return executeUpdate(sql, params);
    }

    @Override
    public int insertOneNews(News news) {
        String sql = "INSERT INTO NEWS(ntid, ntitle, nauthor, ncreateDate, npicPath, ncontent,nmodifyDate, nsummary) VALUES(?,?,?,NOW(),?,?,null,?)";
        Object[] params = {news.getNtid(), news.getNtitle(), news.getNauthor(),news.getNpicPath(), news.getNcontent(), news.getNsummary()};
        return executeUpdate(sql, params);
    }

    @Override
    public int updateOneNews(News news) {
        String sql = "UPDATE NEWS SET ntid=?,ntitle=?,nauthor=?,ncontent=?,nsummary=?,nmodifyDate=NOW() WHERE nid =?";
        Object[] params = {news.getNtid(), news.getNtitle(), news.getNauthor(), news.getNcontent(), news.getNsummary(), news.getNid()};
        return executeUpdate(sql, params);
    }

}
